package com.sifeng.test;

import com.sifeng.utils.MyUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by yang_gsh on 2018/12/2.
 * 每月功能点写入Excel
 */

public class MonthlyFunctionPointsWriteToExcel {

	public static String getInfoFromFunctionPointExcel(String excelPath) throws IOException, InvalidFormatException {
		InputStream iStream=new FileInputStream(excelPath);
		XSSFWorkbook wb = new XSSFWorkbook(iStream);
		XSSFSheet sheet = wb.getSheetAt(0);
		if (sheet == null) {
			return null;
		}

		XSSFRow row_2 = sheet.getRow(1);
		XSSFRow row_4= sheet.getRow(3);
		XSSFRow row_8 = sheet.getRow(7);

		XSSFCell cell_2D = row_2.getCell(3);
		cell_2D.setCellType(Cell.CELL_TYPE_STRING);
		String reqId = cell_2D.getStringCellValue();

		XSSFCell cell_2B = row_2.getCell(1);
		cell_2B.setCellType(Cell.CELL_TYPE_STRING);
		String reqName = cell_2B.getStringCellValue();

		XSSFCell cell_8K = row_8.getCell(10);
		cell_8K.setCellType(Cell.CELL_TYPE_NUMERIC);
		double reqPointNumber = cell_8K.getNumericCellValue();

		XSSFCell cell_4B = row_4.getCell(1);
		cell_4B.setCellType(Cell.CELL_TYPE_STRING);
		String reqPart = cell_4B.getStringCellValue();
		double reqPartNum = 0;

		reqPart = reqPart.replace(";", "；");
		String[] reqPartStr = reqPart.split("；");
		for (String s : reqPartStr) {
			if(s.contains("营销服务研发部")) {
				s = s.replaceAll(":", "：");
				String[] reqPartStrIn = s.split("：");

				NumberFormat nf=NumberFormat.getPercentInstance();
				try {
					Number m=nf.parse(reqPartStrIn[1]);
					double dd=m.doubleValue();
					reqPartNum = reqPointNumber * dd;
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
		}

		wb.close();

		return reqId + "|" + reqName + "|" + Math.round(reqPointNumber) + "|" + Math.round(reqPartNum);

	}

	public static void writeExcel(String path, List<String> list) throws IOException {
		InputStream iStream=new FileInputStream(path);
		XSSFWorkbook wb = new XSSFWorkbook(iStream);
		XSSFSheet sheet = wb.getSheetAt(0);
		if (sheet == null) {
			return;
		}

		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
			XSSFRow row = sheet.getRow(i + 1);
			String[] str = list.get(i).split("\\|");

			XSSFCell cell0 = row.getCell(0);
			XSSFCell cell1 = row.getCell(1);
			XSSFCell cell3 = row.getCell(3);
			XSSFCell cell5 = row.getCell(5);

			cell0.setCellValue(str[0]);
			cell1.setCellValue(str[1]);
			cell3.setCellValue(Integer.parseInt(str[2]));
			cell3.setCellType(Cell.CELL_TYPE_NUMERIC);
			cell5.setCellValue(Integer.parseInt(str[3]));
			cell5.setCellType(Cell.CELL_TYPE_NUMERIC);
		}

		FileOutputStream outputStream = new FileOutputStream(path);

		wb.write(outputStream);
		outputStream.flush();
		wb.close();
	}

	public static void main(String[] args) throws IOException, InvalidFormatException {
		String month = "201910";
		String path = "E:\\hisun\\开发三部\\功能点评估表\\" + month;
		String outFilePath = path + "\\需求列表_201910.xlsx";
		List<String> fileList = MyUtils.searchFile(path, "REQ-", "_原子功能点评估表（内部考核）.xlsx");
		List<String> list = new ArrayList<String>();
		for (String s : fileList) {
			list.add(MonthlyFunctionPointsWriteToExcel.getInfoFromFunctionPointExcel(path + "\\" + s));
		}

		MonthlyFunctionPointsWriteToExcel.writeExcel(outFilePath,list);

		/*String path = "E:\\hisun\\开发三部\\功能点评估表\\201811\\REQ-20181008-1593_外部发券业务选择适用商户时的判定规则改变_原子功能点评估表.xlsx";
		System.out.println(MonthlyFunctionPointsWriteToExcel.getInfoFromFunctionPointExcel(path));*/

		/*String tmp = "REQ-20181009-1593|外部发券业务选 择适用商户时的判定规则|10|8";
		String path = "E:\\hisun\\开发三部\\功能点评估表\\201811\\需求列表_201811 - 副本.xlsx";
		List<String> list = new ArrayList<String>();
		list.add(tmp);
		try {
			MonthlyFunctionPointsWriteToExcel.writeExcel(path,list);
		} catch (IOException e) {
			e.printStackTrace();
		}*/
	}
}
